--EXEC SP_GET_DIST_SO_INVENTORY_DETAILS -1,23, 457,'DISTRIBUTORSALES' 
/*select LOT_ID, Lot_number, Convert(Varchar, Date_In, 101 ) as Date_In,Customer_Id , 
	Product_Id,  Date_code, SUM( QTY_IN ) AS Qty_in, ISNULL( SUM( SHIPPED_QTY ), 0) AS SHIPPED_QTY  
from Distributor_Inventory_lot DIL, product P where DIL.ProcessStep_id = P.ProcessStep_Id 
 and ( Qty_in - ISNULL( Shipped_Qty, 0 ) ) > 0 GROUP BY LOT_ID, LOT_NUMBER, DATE_IN, DATE_CODE, Customer_Id , Product_Id */

ALTER PROC SP_GET_DIST_SO_INVENTORY_DETAILS
(
	@DIST_SO_ID INT,
	@PRODUCT_ID		INT,
    @DISTRIBUTOR_ID	INT,
	@REQUEST_FROM   VARCHAR(50)
)
AS
BEGIN 
	DECLARE @RESULT_TABLE TABLE ( LOT_ID INT, CSLIT_ID INT, LOT_NUMBER VARCHAR(50), DATE_IN VARCHAR(50), DATE_CODE VARCHAR(50), 
								  QTY_IN INT, SHIPPED_QTY INT, PULLED_QTY INT, DISTRIBUTOR_SALES_ORDER_ID INT, DISTRIBUTOR_ID INT , PRODUCT_ID INT,
								   PART_NUMBER VARCHAR(50), DISTRIBUTOR_NAME VARCHAR(100), SALES_ORDER_NO VARCHAR(50), UNIT_COST NUMERIC(18,4) )
	
	DECLARE @PULLED_QTY  INT
	DECLARE @D_CUSTOMER_ID INT 
	DECLARE @D_CSLIT_ID INT 
    DECLARE @D_LOT_NUMBER VARCHAR(50)
	DECLARE @D_Date_In VARCHAR(40)
	DECLARE @D_Date_code VARCHAR(40)
	DECLARE @D_QTY_IN INT 
	DECLARE @D_SHIPPED_QTY INT
	DECLARE @LOT_ID INT
	DECLARE @PART_NUMBER VARCHAR(50)
	DECLARE @CUST_ID INT 
	DECLARE @PRD_ID INT
	DECLARE @PART_NO VARCHAR(50)
	DECLARE @DIST_NAME VARCHAR(100)
	DECLARE @SO_NO		VARCHAR(50)
	DECLARE @UNIT_COST NUMERIC(18,4)
	--DECLARE @DIST_INVENTORY_CURSOR CURSOR

	DECLARE @CURSOR_STRING NVARCHAR(3000)

	IF @REQUEST_FROM = 'DISTRIBUTORSALES'
	BEGIN
		SET @CURSOR_STRING = N'DECLARE DIST_INVENTORY_CURSOR CURSOR FOR SELECT  LOT_ID,CSLIT_ID, LOT_NUMBER, Convert(Varchar, Date_In, 101 ),Customer_Id , 
				Product_Id, Date_code, sum(QTY_IN) as Qty_in, ISNULL( sum(SHIPPED_QTY), 0 ) AS SHIPPED_QTY  
				FROM DISTRIBUTOR_INVENTORY_LOT DIL, PRODUCT P WHERE DIL.ProcessStep_id = P.ProcessStep_Id 
				AND ( Qty_in - ISNULL( Shipped_Qty, 0 ) ) > 0 '
	END
	ELSE
	BEGIN
		SET @CURSOR_STRING = N'DECLARE DIST_INVENTORY_CURSOR CURSOR FOR SELECT  LOT_ID,-1, LOT_NUMBER, Convert(Varchar, Date_In, 101 ),Customer_Id , 
			Product_Id, Date_code, sum(QTY_IN) as Qty_in, ISNULL( sum(SHIPPED_QTY), 0 ) AS SHIPPED_QTY  
			FROM DISTRIBUTOR_INVENTORY_LOT DIL, PRODUCT P WHERE DIL.ProcessStep_id = P.ProcessStep_Id AND ( Qty_in - ISNULL( Shipped_Qty, 0 ) ) > 0 '
	END	
	IF @DISTRIBUTOR_ID > 0
	BEGIN
		SET @CURSOR_STRING = @CURSOR_STRING + ' AND CUSTOMER_ID = ' + CONVERT(VARCHAR, @DISTRIBUTOR_ID )
	END					   	
	IF @PRODUCT_ID > 0     
	BEGIN                  
		SET @CURSOR_STRING = @CURSOR_STRING + ' AND PRODUCT_ID = ' + CONVERT(VARCHAR, @PRODUCT_ID )
	END
	
	IF @REQUEST_FROM = 'DISTRIBUTORSALES'
	BEGIN
		SET @CURSOR_STRING = @CURSOR_STRING + ' GROUP BY LOT_ID, CSLIT_ID, LOT_NUMBER, DATE_IN, DATE_CODE, Customer_Id , Product_Id '
	END
	ELSE
	BEGIN
		SET @CURSOR_STRING = @CURSOR_STRING + ' GROUP BY LOT_ID, LOT_NUMBER, DATE_IN, DATE_CODE, Customer_Id , Product_Id '
	END
	PRINT @CURSOR_STRING
	EXECUTE SP_EXECUTESQL @CURSOR_STRING

	OPEN DIST_INVENTORY_CURSOR
	FETCH NEXT FROM DIST_INVENTORY_CURSOR INTO  @LOT_ID, @D_CSLIT_ID, @D_LOT_NUMBER, @D_Date_In, @CUST_ID, @PRD_ID, @D_Date_code,  @D_QTY_IN, @D_SHIPPED_QTY
	WHILE ( @@FETCH_STATUS = 0 )
   	BEGIN
		IF @D_QTY_IN > 0
		BEGIN
			SET @PULLED_QTY = 0
			SELECT @PULLED_QTY =  ISNULl(PULLED_QTY,0) FROM Dist_Shipping_Lot_Table WHERE LOT_ID = @LOT_ID AND DISTRIBUTOR_SALES_ORDER_ID=@DIST_SO_ID
	
			SELECT @PART_NO = PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = @PRD_ID
			SELECT @DIST_NAME = C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = @CUST_ID
			SELECT @SO_NO = SALESORDER_NO FROM SALES_ORDER WHERE SALESORDER_ID = ( SELECT SALESORDER_ID FROM Customer_Shipping_table Where 
							Customershipping_id = ( select Customershipping_id FROM Customer_shipping_Line_Item_table WHERE CSLIT_ID = @D_CSLIT_ID ) )
			SELECT TOP 1 @UNIT_COST =  COOL_UNIT_COST FROM Customer_Open_Order_LineItem WHERE SALESORDER_ID = ( SELECT SALESORDER_ID FROM Customer_Shipping_table Where 
							Customershipping_id = ( select Customershipping_id FROM Customer_shipping_Line_Item_table WHERE CSLIT_ID = @D_CSLIT_ID ) )
			insert into @RESULT_TABLE ( LOT_ID, CSLIT_ID, Lot_number, DATE_IN, DATE_CODE, QTY_IN, SHIPPED_QTY, PULLED_QTY, DISTRIBUTOR_SALES_ORDER_ID,
										DISTRIBUTOR_ID, PRODUCT_ID,PART_NUMBER, DISTRIBUTOR_NAME,SALES_ORDER_NO,UNIT_COST ) 
			VALUES ( @LOT_ID, @D_CSLIT_ID, @D_LOT_NUMBER, Convert(Varchar, @D_DATE_IN, 101 ), @D_Date_code,  @D_QTY_IN , @D_SHIPPED_QTY, @PULLED_QTY,@DIST_SO_ID
						,@CUST_ID, @PRD_ID, @PART_NO, @DIST_NAME,@SO_NO,@UNIT_COST )
		END
		
		SET @LOT_ID = NULL
		SET @D_QTY_IN = NULL
		SET @D_LOT_NUMBER = NULL
		SET @D_Date_In = NULL
		SET @D_SHIPPED_QTY = NULL
		SET @D_Date_code  = NULL
		SET @D_CSLIT_ID = NULL
		SET @CUST_ID = NULL
		SET @PRD_ID = NULL
		SET @PART_NO = NULL
		SET @SO_NO = NULL
		SET @UNIT_COST = NULL
		FETCH NEXT FROM DIST_INVENTORY_CURSOR INTO  @LOT_ID, @D_CSLIT_ID, @D_LOT_NUMBER, @D_Date_In, @CUST_ID, @PRD_ID, @D_Date_code,  @D_QTY_IN, @D_SHIPPED_QTY
	END
	CLOSE DIST_INVENTORY_CURSOR
	DEALLOCATE DIST_INVENTORY_CURSOR
	--SELECT * FROM @RESULT_TABLE
	SELECT * FROM @RESULT_TABLE where ( Qty_in - ISNULL( Shipped_Qty, 0 )+ ISNULL(pulled_qty,0) ) > 0 Order by Part_Number
END